package com.ynr.common.utils;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;

import com.ynr.common.model.SheetBalance;
import com.ynr.common.model.SheetCash;
import com.ynr.common.model.SheetIncome;

public class ExcelGenerator {
	
	private static final Logger logger = LoggerFactory.getLogger(ExcelGenerator.class);
	
	private static HSSFWorkbook workbook = null;
	private final static String PATH = "/home/excel/template";
	private final static String EXCEL_PATH = "/home/excel/sheet/";
	private static SheetBalance sheetBalance;
	private static SheetIncome sheetIncome;
	private static SheetCash sheetCash;

	public static ResponseEntity<byte[]> generateSheetBalanceExcel(SheetBalance sb, String companyName, String billDate, String sheetType) {
		sheetBalance = sb;
        ResponseEntity<byte[]> re = null;
        String excelName = ExportExcelFile.generateExcelName(companyName, billDate, "资产负债表");
        if (ExportExcelFile.isFileExist(excelName)) {
            ExportExcelFile.removeFile(excelName);
        }
        String filePath = generateSheetExcel("资产负债表", excelName);;
        File file = new File(filePath);
        try {
            HttpHeaders headers = new HttpHeaders();
            headers.setContentDispositionFormData("attachment",excelName);
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            re = new ResponseEntity<byte[]>(
                    FileUtils.readFileToByteArray(file), headers,
                    HttpStatus.OK);
        } catch (IOException e) {
            logger.debug("download exception : {}", e.toString());
        }
        return re;
	}
	
	public static ResponseEntity<byte[]> generateSheetCashExcel(SheetCash sc, String companyName, String billDate, String sheetType) {
		sheetCash = sc;
        ResponseEntity<byte[]> re = null;
        String excelName = ExportExcelFile.generateExcelName(companyName, billDate, "现金流量表");
        if (ExportExcelFile.isFileExist(excelName)) {
            ExportExcelFile.removeFile(excelName);
        } 
        String filePath = generateSheetExcel("现金流量表", excelName);
        File file = new File(filePath);
        try {
            HttpHeaders headers = new HttpHeaders();
            headers.setContentDispositionFormData("attachment",excelName);
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            re = new ResponseEntity<byte[]>(
                    FileUtils.readFileToByteArray(file), headers,
                    HttpStatus.OK);
        } catch (IOException e) {
            logger.debug("download exception : {}", e.toString());
        }
        
        return re;
	}
	
	public static ResponseEntity<byte[]> generateSheetIncomeExcel(SheetIncome si, String companyName, String billDate, String sheetType) {
		sheetIncome = si;
        ResponseEntity<byte[]> re = null;
        String excelName = ExportExcelFile.generateExcelName(companyName, billDate, "利润表");
        if (ExportExcelFile.isFileExist(excelName)) {
            ExportExcelFile.removeFile(excelName);
        } 
        String filePath = generateSheetExcel("利润表", excelName);
        File file = new File(filePath);
        try {
            HttpHeaders headers = new HttpHeaders();
            headers.setContentDispositionFormData("attachment",excelName);
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            re = new ResponseEntity<byte[]>(
                    FileUtils.readFileToByteArray(file), headers,
                    HttpStatus.OK);
        } catch (IOException e) {
            logger.debug("download exception : {}", e.toString());
        }
        
        return re;
	}
	
	public static String generateSheetExcel(String sheetName, String excelName) {
		try {
			workbook = new HSSFWorkbook(new FileInputStream(ExcelGenerator.PATH + "/"+sheetName+"模板.xls"));
			HSSFSheet sheet = workbook.getSheetAt(0);
			List<List<String>> list = readingConfigurationFile(judgePath(sheetName));
			Map<String, List<String>> map = getMethodNameMap(list);
			for (int i = 6; i < sheet.getLastRowNum(); i++) {
				HSSFRow row = sheet.getRow((short) i);
				if (null == row) {
					continue;
				} else {
					if (sheetName.equals("资产负债表")) {
						setExcelBalanceCellValue(row, map);
					} else if (sheetName.equals("利润表")) {
						setExcelIncomeCellValue(row, map);
					} else if (sheetName.equals("现金流量表")) {
						setExcelCashCellValue(row, map);
					}
				}
			}

		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		FileOutputStream out = null;
		try {
			out = new FileOutputStream(excelName);
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (workbook != null) {
					workbook.close();
					out.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return excelName;
	}

	public static void setExcelCashCellValue(HSSFRow row, Map<String, List<String>> map) {
		HSSFCell cellOne = row.getCell((short) 0);
		if (map.get(cellOne.getStringCellValue()) != null) {
			HSSFCell cell2 = row.getCell((short) 2);
			HSSFCell cell3 = row.getCell((short) 3);
			// 通过String 做方法名
			cell2.setCellValue(getExcelCellValue("sheetCash", map.get(cellOne.getStringCellValue().trim()).get(0)));
			cell3.setCellValue(getExcelCellValue("sheetCash", map.get(cellOne.getStringCellValue().trim()).get(1)));
		}
	}

	public static void setExcelIncomeCellValue(HSSFRow row, Map<String, List<String>> map) {
		HSSFCell cellOne = row.getCell((short) 0);
		if (map.get(cellOne.getStringCellValue()) != null) {
			HSSFCell cell2 = row.getCell((short) 2);
			HSSFCell cell3 = row.getCell((short) 3);
			// 通过String 做方法名
			cell2.setCellValue(getExcelCellValue("sheetIncome", map.get(cellOne.getStringCellValue().trim()).get(0)));
			cell3.setCellValue(getExcelCellValue("sheetIncome", map.get(cellOne.getStringCellValue().trim()).get(1)));
		}
	}

	public static void setExcelBalanceCellValue(HSSFRow row, Map<String, List<String>> map) {
		HSSFCell cellOne = row.getCell((short) 0);
		if (map.get(cellOne.getStringCellValue().trim()) != null) {
			HSSFCell cell2 = row.getCell((short) 2);
			HSSFCell cell3 = row.getCell((short) 3);
			cell2.setCellValue(getExcelCellValue("sheetBalance", map.get(cellOne.getStringCellValue().trim()).get(0)));
			cell3.setCellValue(getExcelCellValue("sheetBalance", map.get(cellOne.getStringCellValue().trim()).get(1)));
		}
		HSSFCell cellTwo = row.getCell((short) 4);
		if (map.get(cellTwo.getStringCellValue().trim()) != null) {
			HSSFCell cell6 = row.getCell((short) 6);
			HSSFCell cell7 = row.getCell((short) 7);
			cell6.setCellValue(getExcelCellValue("sheetBalance", map.get(cellTwo.getStringCellValue().trim()).get(0)));
			cell7.setCellValue(getExcelCellValue("sheetBalance", map.get(cellTwo.getStringCellValue().trim()).get(1)));
		}

	}

	public static String getExcelCellValue(String claName, String methodString) {
		String result = "";
		Object boj = null;
		try {
			if (claName.equals("sheetBalance")) {
				Method sheetBalanceMethod = sheetBalance.getClass().getDeclaredMethod(methodString, null);
				result = String.valueOf(sheetBalanceMethod.invoke(sheetBalance, new Object[] {}));
			} else if (claName.equals("sheetIncome")) {
				Method sheetIncomeMethod = sheetIncome.getClass().getDeclaredMethod(methodString, null);
				result = String.valueOf(sheetIncomeMethod.invoke(sheetIncome, new Object[] {}));
			} else if (claName.equals("sheetCash")) {
				Method sheetCashMethod = sheetCash.getClass().getDeclaredMethod(methodString, null);
				result = String.valueOf(sheetCashMethod.invoke(sheetCash, new Object[] {}));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	public static String judgePath(String sheetName) {
		if (sheetName.equals("资产负债表")) {
			return ExcelGenerator.PATH + "/sheet_balance.txt";
		} else if (sheetName.equals("利润表")) {
			return ExcelGenerator.PATH + "/sheet_income.txt";
		} else if (sheetName.equals("现金流量表")) {
			return ExcelGenerator.PATH + "/sheet_cash.txt";
		}
		return null;
	}

	public static List<List<String>> readingConfigurationFile(String path) {
		List<List<String>> infoList = new ArrayList<>();
		File file = new File(path);
		if (file.isFile() && file.exists()) {
			try {
				InputStreamReader input = new InputStreamReader(new FileInputStream(file), "UTF-8");
				BufferedReader reader = new BufferedReader(input);
				String lineText = null;
				while ((lineText = reader.readLine()) != null) {
					infoList.add(disposeString(lineText));
				}
				reader.close();
				input.close();
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return infoList;
	}

	public static Map<String, List<String>> getMethodNameMap(List<List<String>> list) {
		Map<String, List<String>> map = new HashMap<>();
		List<String> properList;
		for (int outerNum = 0; outerNum < list.size(); outerNum++) {
			if (list.get(outerNum).get(0) != null) {
				if (list.get(outerNum).size() < 3) {
					map.put(list.get(outerNum).get(0), null);
				} else {
					properList = new ArrayList<>();
					if (list.get(outerNum).size() == 3) {
						properList.add(splitProperList(list.get(outerNum).get(1)));
						properList.add(splitProperList(list.get(outerNum).get(2)));
					}
					map.put(list.get(outerNum).get(0), properList);
				}
			}
		}
		return map;
	}

	public static String splitProperList(String methodString) {
		String[] methodArrays = methodString.split("_");
		StringBuffer buffer = new StringBuffer();
		buffer.append("get");
		for (int i = 0; i < methodArrays.length; i++) {
			buffer.append(captureName(methodArrays[i]));
		}
		return buffer.toString();
	}

	public static String captureName(String methodName) {
		char[] cs = methodName.toCharArray();
		cs[0] -= 32;
		return String.valueOf(cs);
	}

	public static List<String> disposeString(String readLineString) {
		String[] txts = null;
		List<String> list = new ArrayList<>();
		if (readLineString.indexOf(" ") != -1) {
			txts = readLineString.split(" ");
			for (int num = 0; num < txts.length; num++) {
				if (txts[num].length() > 1) {
					list.add(txts[num]);
				}
			}
		} else {
			list.add(readLineString);
		}
		return list;
	}
}
